﻿Imports System.Data.OleDb
Imports System.DBNull
Imports System.Data.SqlClient
Public Class CTnhapsach
    Public db As New Class1
    Dim gt As Boolean
    Public sql As String
    Public mycom As OleDbCommand
    Dim con As OleDbConnection

    Private Sub CTnhapsach_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        db.connectDB()
        showDataGrid()
        sql = "select * from NhapSach"
        db.strSQL(sql)
        While db.myda.Read()
            cbnhapsach.Items.Add(db.myda.GetString(0))
        End While
        sql = "select * from LoaiSach"
        db.strSQL(sql)
        While db.myda.Read()
            cbmaloaisach.Items.Add(db.myda.GetString(0))
        End While
        sql = "select * from DauSach"
        db.strSQL(sql)
        While db.myda.Read()
            cbmadausach.Items.Add(db.myda.GetString(0))
        End While
        khoa()
        viewCTnhapsach.DataSource = db.mydataset.Tables(0)
        viewCTnhapsach.Columns(0).Width = 118.125
        viewCTnhapsach.Columns(1).Width = 118.125
        viewCTnhapsach.Columns(2).Width = 118.125
        viewCTnhapsach.Columns(3).Width = 118.125
        viewCTnhapsach.Columns(4).Width = 118.125
        viewCTnhapsach.Columns(5).Width = 118.125
        viewCTnhapsach.Columns(6).Width = 118.125
        viewCTnhapsach.Columns(7).Width = 118.125
    End Sub
    Sub khoa()
        Me.maCTNS.Enabled = False
        Me.cbnhapsach.Enabled = False
        Me.cbmaloaisach.Enabled = False
        Me.cbmadausach.Enabled = False
        Me.soluong.Enabled = False
        Me.dongia.Enabled = False
        Me.ghichu.Enabled = False
        Me.ngaynhap.Enabled = False
    End Sub
    Sub mo()
        Me.maCTNS.Enabled = True
        Me.cbnhapsach.Enabled = True
        Me.cbmaloaisach.Enabled = True
        Me.cbmadausach.Enabled = True
        Me.soluong.Enabled = True
        Me.dongia.Enabled = True
        Me.ghichu.Enabled = True
        Me.ngaynhap.Enabled = True
    End Sub
    Sub xoa()
        Me.maCTNS.Text = ""
        Me.cbmaloaisach.Text = "chọn..."
        Me.cbnhapsach.Text = "chọn..."
        Me.cbmadausach.Text = "chọn..."
        Me.soluong.Text = ""
        Me.dongia.Text = ""
        Me.ghichu.Text = ""
    End Sub
    Function samekey()
        Dim lenh As String = " Select MaCTNS from CTNhapSach where MaCTNS='" & Me.maCTNS.Text & "'"
        Dim mycom As New OleDbCommand(lenh, db.myconn)
        Dim mydr As OleDbDataReader = mycom.ExecuteReader()
        Return mydr.HasRows()
    End Function
    Sub showDataGrid()
        sql = "Select  MaCTNS as 'Mã CT Nhập Sách' , MaNS as 'Mã Nhập Sách', MaLS as 'Mã Loại Sách', MaDS as 'Mã Đầu Sách', SoLuong  as 'Số Lượng', DonGia  as 'Đơn Giá', GhiChu  as 'Ghi Chú', SoLuong*DonGia as 'Thành Tiền' From CTNhapSach"
        db.iniDataset(sql)
        Me.viewCTnhapsach.DataSource = db.mydataset.Tables(0)
    End Sub
    Private Sub them()
        showDataGrid()
        Dim sql As String = "insert into CTNhapSach values(N'" & Me.maCTNS.Text.ToUpper & "',N'" & Me.cbnhapsach.Text & "',N'" & Me.cbmaloaisach.Text & "',N'" & Me.cbmadausach.Text & "',N'" & Me.soluong.Text & "',N'" & Me.dongia.Text & "',N'" & Me.ghichu.Text & "');"
        If samekey() = True Then
            MsgBox("Mã CT Nhập Sách bạn nhập hiện đã có trong CSDL. Bạn vui lòng nhập lại!")
            Me.maCTNS.Focus()
        Else
            db.doSQL(sql)
            MsgBox("Chúc mừng! Bạn đã thêm thành công!")
        End If
        showDataGrid()
    End Sub
    Sub sua()
        sql = "UPDATE CTNhapSach SET  MaNS=N'" + Me.cbnhapsach.Text + "', MaLS=N'" + Me.cbmaloaisach.Text + "', MaDS=N'" + Me.cbmadausach.Text + "', SoLuong=N'" + Me.soluong.Text + "', DonGia=N'" + Me.dongia.Text + "', GhiChu=N'" + Me.ghichu.Text + "' where MaCTNS=N'" + Me.maCTNS.Text.ToUpper + "'"
        db.doSQL(sql)
        Me.showDataGrid()
    End Sub

    Private Sub btthem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btthem.Click
        db.connectDB()
        xoa()
        btluu.Enabled = True
        bthuy.Enabled = True
        btthem.Enabled = False
        btsua.Enabled = False
        mo()
        gt = True
        Me.viewCTnhapsach.Enabled = False
    End Sub

    Private Sub btxoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btxoa.Click
        db.connectDB()
        sql = "delete From CTNhapSach where MaCTNS='" & Me.maCTNS.Text & "'"
        If Me.maCTNS.Text <> "" Then
            If MsgBox("Bạn có thật sự muốn xóa các thông tin về " & Me.maCTNS.Text, MsgBoxStyle.OkCancel, "Xác nhận") = MsgBoxResult.Ok Then
                db.doSQL(sql)
            End If
        Else : MsgBox("Bạn chưa chọn thông tin cần xóa", MsgBoxStyle.Information, "Thông báo")
        End If
        Me.showDataGrid()
    End Sub

    Private Sub btsua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btsua.Click
        db.connectDB()

        If Me.maCTNS.Text = "" Then
            MsgBox("chua chua truong de sua")
        Else
            Me.viewCTnhapsach.Enabled = False
            btluu.Enabled = True
            bthuy.Enabled = True
            btthem.Enabled = False
            btsua.Enabled = False
            mo()
            Me.maCTNS.Enabled = False
            gt = False
        End If
    End Sub

    Private Sub btluu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btluu.Click
        If gt = True Then
            them()
        Else
            sua()
        End If
    End Sub

    Private Sub bthuy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bthuy.Click
        btluu.Enabled = False
        bthuy.Enabled = False
        btthem.Enabled = True
        btsua.Enabled = True
        khoa()
        xoa()
        Me.viewCTnhapsach.Enabled = True
    End Sub

    Private Sub viewCTnhapsach_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles viewCTnhapsach.CellContentClick
        Try
            Me.maCTNS.Text = viewCTnhapsach.CurrentRow.Cells(0).Value()
            Me.cbnhapsach.Text = viewCTnhapsach.CurrentRow.Cells(1).Value()
            Me.cbmaloaisach.Text = viewCTnhapsach.CurrentRow.Cells(2).Value()
            Me.cbmadausach.Text = viewCTnhapsach.CurrentRow.Cells(3).Value()
            Me.soluong.Text = viewCTnhapsach.CurrentRow.Cells(4).Value()
            Me.dongia.Text = viewCTnhapsach.CurrentRow.Cells(5).Value()
            Me.ghichu.Text = viewCTnhapsach.CurrentRow.Cells(6).Value()

        Catch ex As Exception
        End Try
    End Sub

    Private Sub cbnhapsach_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbnhapsach.SelectedIndexChanged
        sql = "SELECT     dbo.NhapSach.NgayNhap, dbo.NhapSach.MaNS " _
            & "FROM         dbo.CTNhapSach INNER JOIN dbo.NhapSach ON dbo.CTNhapSach.MaNS = dbo.NhapSach.MaNS " _
            & "WHERE    dbo.NhapSach.MaNS =N'" & Me.cbnhapsach.SelectedItem & "'"
        mycom = New OleDbCommand(sql, db.myconn)
        Try
            Dim obj As Object
            obj = mycom.ExecuteScalar
            Me.ngaynhap.Value = obj.ToString()
        Catch ex As Exception
            Me.ngaynhap.Value = ""
        End Try
    End Sub
End Class